﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HuseData
{
    public class DataAccess
    {
        /// <summary>
        /// 检测数据是否已经存在
        /// </summary>
        /// <param name="day"></param>
        /// <param name="DataType"></param>
        /// <param name="Area"></param>
        /// <returns></returns>
        public bool CheckDataInData(DateTime day, int DataType, int Area)
        {
            string sql = string.Format("select count(*) from ShangHaiHouseTable where day='{0}' and DataType={1} and Area={2}",day.ToString("yyyy-MM-dd"),DataType,Area);
            if (Convert.ToInt32(SqlHelper.ExecuteScalar(HuseData.Properties.Settings.Default.DataBase, CommandType.Text, sql)) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="day"></param>
        /// <param name="DataType"></param>
        /// <param name="Area"></param>
        /// <param name="num"></param>
        /// <returns></returns>
        public int InsertData(DateTime day, int DataType, int Area, double num)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("insert into ShangHaiHouseTable (day,datatype,area,num,row)values('{0}',{1},{2},{3},'{4}');",day.ToString("yyyy-MM-dd"),
                DataType,Area,num,DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));

            return SqlHelper.ExecuteNonQuery(HuseData.Properties.Settings.Default.DataBase, CommandType.Text, sb.ToString());

        }


        /// <summary>
        /// 根据数据类型获取数据
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, HouseDataEntity> GetHouseData(int datatype, int Area, ReportDateType countdatetype)
        {

            Dictionary<string, HouseDataEntity> lst = new Dictionary<string, HouseDataEntity>();
            //List<HouseDataEntity> lst = new List<HouseDataEntity>();

            StringBuilder sb = new StringBuilder();

            if (countdatetype == ReportDateType.Month)
            {
                sb.AppendFormat("select CONVERT(varchar(7) , day, 121) as Day,max(Num) from ShangHaiHouseTable where datatype={0} and Area={1}  group by  CONVERT(varchar(7) , day, 121) order by day asc", datatype, Area);
            }
            else if (countdatetype == ReportDateType.Week)
            { 
                sb.AppendFormat(@"select cast(year(day) as varchar(4))+'-'+DateName (week,day) as Day,max(Num) from ShangHaiHouseTable
                                                where datatype={0} and Area={1}
                                                 group by 
                                                cast(year(day) as varchar(4))+'-'+DateName (week,day) order by day asc", datatype, Area);
                
            }
            else
            {
                sb.AppendFormat("select * from ShangHaiHouseTable where datatype={0} and Area={1} order by day asc ", datatype, Area);
            }

            using (SqlDataReader dr = SqlHelper.ExecuteReader(HuseData.Properties.Settings.Default.DataBase, CommandType.Text, sb.ToString()))
            {
                while (dr.Read())
                {
                    HouseDataEntity hde = new HouseDataEntity();
                    if (countdatetype == ReportDateType.Month)
                    {
                        hde.Num = Convert.ToDouble(dr[1]);

                        lst.Add(dr.GetString(0), hde);
                    }
                    else if (countdatetype == ReportDateType.Week)
                    {
                        hde.Num = Convert.ToDouble(dr[1]);

                        lst.Add(dr.GetString(0), hde);
                    }
                    else
                    {
                        hde.Area = dr.GetInt32(3);
                        hde.DataType = dr.GetInt32(2);
                        hde.Day = dr.GetDateTime(1);
                        hde.Num = Convert.ToDouble(dr[4]);
                        lst.Add(hde.Day.ToString("yyyy-MM-dd"), hde);
                    }
                    
                }
                dr.Close();
                dr.Dispose();
            }

            return lst;
        }

        /// <summary>
        /// 获得数据
        /// </summary>
        /// <returns></returns>
        public List<DateTime> GetHouseDayAll()
        {
            List<DateTime> lst = new List<DateTime>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select Day from ShangHaiHouseTable group by day order by day asc ");

            using (SqlDataReader dr = SqlHelper.ExecuteReader(HuseData.Properties.Settings.Default.DataBase, CommandType.Text, sb.ToString()))
            {
                while (dr.Read())
                {
                    lst.Add(dr.GetDateTime(0));
                }
                dr.Close();
                dr.Dispose();
            }

            return lst;
        }

        /// <summary>
        /// 获得数据
        /// </summary>
        /// <returns></returns>
        public List<string> GetHouseMonthAll()
        {
            List<string> lst = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select CONVERT(varchar(7) , day, 121) as Day from ShangHaiHouseTable group by CONVERT(varchar(7) , day, 121) order by day asc");

            using (SqlDataReader dr = SqlHelper.ExecuteReader(HuseData.Properties.Settings.Default.DataBase, CommandType.Text, sb.ToString()))
            {
                while (dr.Read())
                {
                    lst.Add(dr.GetString(0).Trim());
                }
                dr.Close();
                dr.Dispose();
            }

            return lst;
        }

        /// <summary>
        /// 获得数据
        /// </summary>
        /// <returns></returns>
        public List<string> GetHouseWeekAll()
        {
            List<string> lst = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select cast(year(day) as varchar(4))+'-'+DateName (week,day) as Day from ShangHaiHouseTable group by cast(year(day) as varchar(4))+'-'+DateName (week,day) order by day asc");

            using (SqlDataReader dr = SqlHelper.ExecuteReader(HuseData.Properties.Settings.Default.DataBase, CommandType.Text, sb.ToString()))
            {
                while (dr.Read())
                {
                    lst.Add(dr.GetString(0).Trim());
                }
                dr.Close();
                dr.Dispose();
            }

            return lst;
        }
    }
}
